﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Odbc;
using System.Configuration;

public class Usuario
{
    public int idCadastroUsuario { get; set; }
    public string NomeCompleto { get; set; }
    public string Email { get; set; }
    public string Senha { get; set; }
    public string msgRetorno { get; set; }
    public int codRetorno { get; set; }
    public int idnextval { get; set; }

    public string comandoDelete = "DELETE FROM tbCadastroUsuario WHERE idCadastroUsuario = ? ";
    public string comandoSelect = "SELECT * FROM tbCadastroUsuario WHERE idCadastroUsuario = ? ";
    public string comandoInsert = "INSERT INTO tbCadastroUsuario (Email, Senha) VALUES (?,?) ";
    public string comandoUpdate = "UPDATE tbCadastroUsuario SET Email=?, Senha=? WHERE idCadastroUsuario = ? ";

    public Usuario()
    {
        this.idCadastroUsuario = 0;
        this.NomeCompleto = "";
        this.Email = "";
        this.Senha = "";
    }
    public static List<Usuario> SelecionarTodos(string param)
    {
        try
        {
            string comandSelectTodos = "SELECT * FROM tbCadastroUsuario ";

            if (param != "")
            {
                //comandSelectTodos = "SELECT * FROM tbUsuario WHERE Login LIKE '%" + param.ToString() + "%' ";
                comandSelectTodos = "SELECT * FROM tbCadastroUsuario WHERE Email = ? ";
            }

            using (OdbcConnection con = new OdbcConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
            using (OdbcCommand cmd = new OdbcCommand(comandSelectTodos, con))
            {
                List<Usuario> lUsuario = new List<Usuario>();

                cmd.Parameters.Add("@Email", OdbcType.VarChar, 255).Value = param; 
                con.Open();
                OdbcDataReader rs = cmd.ExecuteReader();
                while (rs.Read())
                {
                    
                    Usuario oConv = new Usuario();
                    oConv.idCadastroUsuario = Convert.ToInt32(rs["idCadastroUsuario"]);
                    oConv.NomeCompleto = rs["NomeCompleto"].ToString();
                    oConv.Email = rs["Email"].ToString();
                    oConv.Senha = rs["Senha"].ToString();
                    oConv.msgRetorno = "Registo encontrado";
                    oConv.codRetorno = 0;
                    lUsuario.Add(oConv);
                }
                rs.Close();
                con.Close();
                return lUsuario;
            }
        }
        catch (Exception ex)
        {

            List<Usuario> lUsuario = new List<Usuario>();
            Usuario oConv = new Usuario();
            oConv.msgRetorno = "Registro nao encontrado";
            oConv.codRetorno = 1;
            lUsuario.Add(oConv);
            return lUsuario;

        }
    } // SelecionarTodos
    public int next_val()
    {
        try
        {
            using (OdbcConnection con = new OdbcConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
            using (OdbcCommand cmd = new OdbcCommand("SELECT idCadastroUsuario FROM tbCadastroUsuario ORDER BY idCadastroUsuario DESC LIMIT 0,1 ", con))
            {
                con.Open();
                OdbcDataReader rs = cmd.ExecuteReader();
                while (rs.Read())
                {
                    idCadastroUsuario = Convert.ToInt32(rs["idCadastroUsuario"]) + 1;
                }
            }
            return idnextval;

        }
        catch (Exception ex)
        {
            return 0;
        }

    }
    public void SelecionarPorID(int id)
    {
        try
        {
            using (OdbcConnection con = new OdbcConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
            using (OdbcCommand cmd = new OdbcCommand(comandoSelect, con))
            {
                cmd.Parameters.Add("@idCadastroUsuario", OdbcType.Int).Value = id;
                con.Open();
                OdbcDataReader rs = cmd.ExecuteReader();
                while (rs.Read())
                {
                    this.idCadastroUsuario = Convert.ToInt32(rs["idCadastroUsuario"]);
                    this.NomeCompleto = rs["NomeCompleto"].ToString();
                    this.Email = rs["Email"].ToString();
                    this.Senha = rs["Senha"].ToString();
                    this.msgRetorno = "Registro encontrado";
                    this.codRetorno = 0;
                }
                rs.Close();
                con.Close();
            }

        }
        catch (Exception ex)
        {
            this.msgRetorno = "Registro não encontrado";
            this.codRetorno = 1;
        }
    } // SelecionarPorID
    public void Insert()
    {
        using (OdbcConnection con = new OdbcConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
        using (OdbcCommand cmd = new OdbcCommand(comandoInsert, con))
        {
            //cmd.Parameters.Add("@idUsuarioCadastro", OdbcType.Int).Value = next_val();
            cmd.Parameters.Add("@Email", OdbcType.VarChar, 255).Value = this.Email;
            cmd.Parameters.Add("@Senha", OdbcType.VarChar, 255).Value = this.Senha;
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
            this.msgRetorno = "Registro inserido com sucesso";
            this.codRetorno = 0;
        }
    }
    public void Update(int id)
    {
        using (OdbcConnection con = new OdbcConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
        using (OdbcCommand cmd = new OdbcCommand(comandoUpdate, con))
        {
            cmd.Parameters.Add("@Email", OdbcType.VarChar, 255).Value = this.Email;
            cmd.Parameters.Add("@Senha", OdbcType.VarChar, 255).Value = this.Senha;
            cmd.Parameters.Add("@idCadastroUsuario", OdbcType.Int).Value = id;
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
            this.msgRetorno = "Registro atualizado com sucesso";
            this.codRetorno = 0;
        }
    }
    public void Delete(int id)
    {
        using (OdbcConnection con = new OdbcConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
        using (OdbcCommand cmd = new OdbcCommand(comandoDelete, con))
        {

            cmd.Parameters.Add("@idCadastroUsuario", OdbcType.Int).Value = id;
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }
    } //Delete
    public Boolean Valida(string email, string senha) 
    {
        Boolean retorno =  false;
        using (OdbcConnection con = new OdbcConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
        using (OdbcCommand cmd = new OdbcCommand("SELECT idCadastroUsuario, NomeCompleto, Email, Senha FROM tbCadastroUsuario WHERE Email = ? AND Senha = ?", con))
        {
            cmd.Parameters.Add("@Email", OdbcType.VarChar, 255).Value = email;
            cmd.Parameters.Add("@Senha", OdbcType.VarChar, 255).Value = senha;
            con.Open();
            OdbcDataReader rs = cmd.ExecuteReader();
            while (rs.Read()) {
                if (email.ToString().Equals(rs["Email"].ToString()) && senha.Equals(rs["Senha"].ToString()))
                {
                    retorno = true;
                }
            }
        }
        return retorno;
    } // Valida

}